import pymysql
import openpyxl

#数据库连接
mysqldb = pymysql.connect(
    host ='localhost',
    user = 'root',
    password ='123456',
    port = 3306,
    charset= 'utf8',
    database='test7_7'
)
mycursor = mysqldb.cursor()


val=[]
#excel连接
sql = 'insert into user_info values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
wb = openpyxl.load_workbook('C:/Users/34538/Desktop/ddg.xlsx')
sheet = wb['test']
val =[]
for row in sheet.iter_rows(min_row=1,max_row=2000,values_only=True):
    row_values = tuple(cell for cell in row)
    val.append(row_values)
sql='insert into user_info values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'

# for i in range(len(val)):
#     mycursor.execute(sql,val[i])
# mycursor.execute('select * from user_info')
# a =mycursor.fetchall()
# for j in a:
#     print(j,end ='\n')
# print('查看{}行数据'.format(mycursor.rowcount))
mycursor.executemany(sql,val)
mycursor.execute('select * from user_info')
a = mycursor.fetchall()
print(a)
print('查看{}行数据'.format(mycursor.rowcount))
# mysqldb.commit()
# mycursor.close()
# mysqldb.close()
# wb.close()
